<?php

declare(strict_types=1);

namespace app\common\model;

use app\admin\controller\choujiang\Log;
use think\facade\Db;
use think\Model;
use think\facade\Session;
use think\model\concern\SoftDelete;
use think\facade\Log as Logg;

class BlackboxPlayerinfo extends Model
{
    use SoftDelete;

    protected $deleteTime = "delete_time";

    // 获取列表
    public static function getList()
    {
        $where = [];
        $limit = input('get.limit');

        // 如果不是超级管理员，添加限制条件
        if (Session::get('admin.id') != '1') {
            $where[] = ['b.acode', '=', Session::get('admin.rlink')];
        }
        //按actorid查找
        if ($actorid = input("actorid")) {
            $where[] = ["a.actorid", "like", "%" . $actorid . "%"];
        }
        //按账号查找
        if ($account = input("account")) {
            $where[] = ["a.account", "like", "%" . $account . "%"];
        }
        //按区服查找
        if ($qufu = input("qufu")) {
            $where[] = ["a.qufu", "like", "%" . $qufu . "%"];
        }

        // 构建 SQL 查询语句
        $sql = Db::table('blackbox_playerinfo')
            ->alias('a')
            ->join('gm_tglogs b', 'a.account=b.baccount')
            ->field(' a.id as id,a.actorid as actorid,a.account as account,a.qufu as qufu,a.opennum as opennum,a.cjcoin as cjcoin,a.isdefaultcoin as isdefaultcoin,b.acode as acode ')
            ->where($where)
            ->order('a.actorid', 'desc')
            ->paginate($limit);

        return ['code' => 0, 'data' => $sql->items(), 'extend' => ['count' => $sql->total(), 'limit' => $limit]];
    }

    // 一键清理超级金币限制
    public static function onekeyCoinLimit($serverid)
    {
        $sql = "UPDATE blackbox_playerinfo SET isdefaultcoin=1 WHERE qufu='$serverid';";
        Db::execute($sql);
    }

    // 一键清理超级金币限制
    public static function onekeyCoinLimitNoServerid()
    {
        $sql = "UPDATE blackbox_playerinfo SET isdefaultcoin=1;";
        Db::execute($sql);
    }

    // 一键清理超级金币限制
    public static function onePlayerCoinLimit($actorid, $account, $qufu)
    {
        $sql = "UPDATE blackbox_playerinfo SET isdefaultcoin=1 WHERE  actorid='$actorid' AND account='$account' AND qufu='$qufu';";
        Db::execute($sql);
    }

    // 单个玩家发送超级金币
    public static function sendCoin($actorid, $account, $qufu, $num, $nowcoin)
    {
        // 开启事务
        Db::startTrans();

        try {
            // 执行第一个 SQL 语句
            $sql1 = "UPDATE blackbox_playerinfo SET isdefaultcoin=2,cjcoin=cjcoin+$num WHERE  actorid='$actorid' AND account='$account' AND qufu='$qufu';";
            Db::execute($sql1);

            $create_time = date('Y-m-d H:i:s');
            // 执行第二个 SQL 语句
            $sql2 = "INSERT INTO blackbox_activiteinfo (`actorid`, `account`, `qufu`, `handlers`, `coinchange`, `nowcoin`, `create_time`) 
            VALUES ('$actorid', '$account', '$qufu', '后台发送金币', '+$num', '$nowcoin', '$create_time');";
            Db::execute($sql2);

            // 提交事务
            Db::commit();
        } catch (\Exception $e) {
            // 如果出现异常，回滚事务
            Db::rollback();
            // 可以在这里处理异常，比如记录日志等
            // 注意：这里最好不要抛出异常，以免影响调用方的执行流程
        }
    }

    // 查询玩家信息
    public static function playerInfo($actorid, $account, $qufu)
    {
        return Db::query("SELECT * FROM blackbox_playerinfo WHERE  actorid='$actorid' AND account='$account' AND qufu='$qufu';");
    }

    // 添加玩家信息
    public static function addPlayer($actorid, $account, $qufu, $nikename)
    {
        $create_time = date('Y-m-d H:i:s');
        $sql = "INSERT INTO blackbox_playerinfo (`actorid`, `account`, `qufu`,`nikename`, `create_time`) VALUES('$actorid','$account','$qufu','$nikename','$create_time');";
        Db::execute($sql);
    }

    // actorid验证  返回玩家名称
    public static function actoridCheck($actorid, $account, $qufu)
    {
        $newserverid = self::getDBName($qufu);
        return Db::query("select actorname from $newserverid.actors where accountname='$account' and actorid='$actorid';")[0]["actorname"];
    }

    // 扣减金币
    public static function consumeCoin($actorid, $account, $qufu, $xiaohao, $nowcoin, $opennum, $winners)
    {
        // 开启事务
        Db::startTrans();

        try {
            // 执行第一个 SQL 语句
            $sql1 = "UPDATE blackbox_playerinfo SET cjcoin=cjcoin-$xiaohao,opennum=opennum+$opennum WHERE  actorid='$actorid' AND account='$account' AND qufu='$qufu';";
            Db::execute($sql1);

            $create_time = date('Y-m-d H:i:s');
            // 执行第二个 SQL 语句
            $sql2 = "INSERT INTO blackbox_activiteinfo (`actorid`, `account`, `qufu`, `handlers`, `coinchange`, `nowcoin`, `create_time`) 
            VALUES ('$actorid', '$account', '$qufu', '消耗金币', '-$xiaohao', '$nowcoin', '$create_time');";
            Db::execute($sql2);

            // 执行第三个 SQL 语句
            $win = json_encode($winners, JSON_UNESCAPED_UNICODE);
            $sql3 = "INSERT INTO blackbox_boardlist (`actorid`, `account`, `qufu`, `num`, `xiaohao`, `reward`, `create_time`) 
            VALUES ('$actorid', '$account', '$qufu', '$opennum', '$xiaohao', '$win', '$create_time');";
            Db::execute($sql3);

            // 提交事务
            Db::commit();
            return "操作成功";
        } catch (\Exception $e) {
            // 如果出现异常，回滚事务
            Db::rollback();

            // 记录错误信息
            return 'Error: ' . $e->getMessage(); // 输出异常消息
            // return 'Error Code: ' . $e->getCode(); // 输出异常代码
            // 可以在这里处理异常，比如记录日志等
            // 注意：这里最好不要抛出异常，以免影响调用方的执行流程
        }
    }


    // 平台币查询
    public static function getPTBNum($account, $qufu)
    {
        $newserverid = self::getDBName($qufu);
        return Db::query("select rmb from $newserverid.globaluser where account='$account';")[0]["rmb"];
    }

    // 超级金币兑换
    public static function duihuanCjcoin($account, $actorid, $qufu, $cjcoinnum, $ptb, $nowcoin, $oldrmb)
    {
        $newserverid = self::getDBName($qufu);

        // 开启事务
        Db::startTrans();

        try {
            // 扣减平台币
            $sql1 = "UPDATE $newserverid.globaluser SET rmb=rmb-$ptb WHERE  account='$account';";
            Db::execute($sql1);

            // 增加金币
            $sql2 = "UPDATE blackbox_playerinfo SET cjcoin=cjcoin+$cjcoinnum  WHERE  actorid='$actorid' AND account='$account' AND qufu='$qufu';";
            Db::execute($sql2);

            // 金币增长记录
            $create_time = date('Y-m-d H:i:s');
            $sql3 = "INSERT INTO blackbox_activiteinfo (`actorid`, `account`, `qufu`, `handlers`, `coinchange`, `nowcoin`, `create_time`) 
            VALUES ('$actorid', '$account', '$qufu', '游戏内充值', '+$cjcoinnum', '$nowcoin', '$create_time');";
            Db::execute($sql3);

            //平台币消耗记录
            $nowrmb = $oldrmb - $ptb;
            $sql4 = "INSERT INTO gm_conslog (`serverid`, `qufu`, `account`, `oldrmb`, `nowrmb`, `consrmb`, `itemid`, `xftime`) 
            VALUES ('$qufu', '$newserverid', '$account', '$oldrmb', '$nowrmb', '-$ptb', '充值超级金币', NOW());";
            Db::execute($sql4);


            // 提交事务
            Db::commit();
            return "操作成功";
        } catch (\Exception $e) {
            // 如果出现异常，回滚事务
            Db::rollback();

            // 记录错误信息
            return 'Error: ' . $e->getMessage(); // 输出异常消息
            // return 'Error Code: ' . $e->getCode(); // 输出异常代码
            // 可以在这里处理异常，比如记录日志等
            // 注意：这里最好不要抛出异常，以免影响调用方的执行流程
        }
    }


    // 配置信息
    public static function configInfo()
    {
        return Db::query("SELECT * FROM blackbox_config Limit 1")[0];
    }


    // 修改配置
    public static function configMo($activiteisopen, $desc, $typels, $dhbl, $daysendgoodsls, $allsendgoodsls, $sendcoinlimit, $onenumcoin, $rankdaynum, $rankallnum)
    {
        $sql = "UPDATE blackbox_config SET activiteisopen=?, `desc`=?, typels=?, dhbl=?, daysendgoodsls=?, allsendgoodsls=?, sendcoinlimit=?, onenumcoin=?, rankdaynum=?, rankallnum=?";
        Db::execute($sql, [$activiteisopen, $desc, $typels, $dhbl, $daysendgoodsls, $allsendgoodsls, $sendcoinlimit, $onenumcoin, $rankdaynum, $rankallnum]);
    }

    // 获取所有抽奖物品
    public static function getRewardgoodsItems()
    {
        return Db::query("SELECT * FROM blackbox_rewardgood WHERE delete_time IS NULL;");
    }

    // 玩家排行
    public static function getPlayerRank($account, $actorid, $qufu, $date = null)
    {
        // 查询 trandingbank_kfconfig 表，获取符合条件的 statrid 和 endid
        $config = Db::table('blackbox_cfconfig')
            ->where('statrid', '<=', $qufu)
            ->where('endid', '>=', $qufu)
            ->select();

        if (empty($config)) {
            return 0; // 或者其他适当的默认值
        }

        $startid = $config[0]['statrid'];
        $endid = $config[0]['endid'];

        // 初始化 SQL 查询
        $sql = "SELECT
                actorid,
                account,
                qufu,
                total_num,
                (@rank := @rank + 1) AS rank_position
            FROM (
                SELECT
                    actorid,
                    account,
                    qufu,
                    SUM(num) AS total_num
                FROM blackbox_boardlist";

        // 根据是否指定日期来决定是否添加日期过滤条件
        if ($date !== null) {
            $sql .= " WHERE DATE(create_time) = ? AND qufu >= ? AND qufu <= ?";
        } else {
            $sql .= " WHERE qufu >= ? AND qufu <= ?";
        }

        $sql .= " GROUP BY actorid, account, qufu
            ) AS grouped_data
            CROSS JOIN (SELECT @rank := 0) AS r
            ORDER BY total_num DESC"; // 按照 total_num 降序排列

        // 使用占位符的预处理语句
        if ($date !== null) {
            $playerRank = Db::query($sql, [$date, $startid, $endid]);
        } else {
            $playerRank = Db::query($sql, [$startid, $endid]);
        }

        // 查找指定玩家的排名
        $playerPosition = 0;
        foreach ($playerRank as $key => $player) {
            if ($player['actorid'] == $actorid && $player['account'] == $account && $player['qufu'] == $qufu) {
                $playerPosition = $player['rank_position'];
                break;
            }
        }

        return $playerPosition;
    }


    // 获取盲盒榜单
    public static function getBroad($qufu, $date = null, $designationDate = null)
    {
        // 查询 trandingbank_kfconfig 表，获取符合条件的 statrid 和 endid
        $config = Db::table('blackbox_cfconfig')
            ->where('statrid', '<=', $qufu)
            ->where('endid', '>=', $qufu)
            ->find();

        // 如果没有找到符合条件的记录，直接返回空数据
        if (empty($config)) {
            return [];
        }

        // 构建查询条件
        $where = [
            ['qufu', '>=', $config['statrid']],
            ['qufu', '<=', $config['endid']]
        ];

        // 根据是否指定日期来决定是否添加日期过滤条件
        if ($date !== null) {
            $startDate = $date . ' 00:00:00';
            $endDate = $date . ' 23:59:59';
        }

        // 使用查询构建器来构建查询
        $query = Db::table('blackbox_boardlist')
            ->field("CONCAT(SUBSTRING(account, 1, 1), '***', SUBSTRING(account, -1)) AS account, qufu, SUM(num) AS total_num")
            ->where($where);

        // 添加日期条件
        if ($date !== null) {
            $query->where('create_time', 'between time', [$startDate, $endDate]);
        }

        // 执行查询并处理返回的数据格式
        $data = $query->group('actorid, account, qufu')
            ->order('total_num', 'desc')
            ->limit(50)
            ->select();

        // 处理返回的数据格式
        $result = [];
        foreach ($data as $row) {
            $result[] = [
                'account' => $row['account'],
                'qufu' => $row['qufu'],
                'total_num' => $row['total_num']
            ];
        }

        // 返回数据
        return $result;
    }

    //发送榜单奖励
    public static function sendReward($type, $startTime = null, $endTime = null, $startId = null, $endId = null)
    {
        // 获取物品配置
        $gameconfig = self::configInfo();

        // 获取跨服组配置
        $kfGroups = Db::query("SELECT * FROM blackbox_cfconfig");

        // 存储要返回的调试信息
        $debugInfo = [];
        if ($type == 'all') {
            $kfGroupName = '跨服组' . $startId . "---" . $endId;
            //修正结束时间为00:00:00
            $startTime = date('Y-m-d', strtotime($startTime)) . ' 00:00:00';
            $endTime = strtotime($endTime . ' 23:59:59') - 86400; // 86400 秒是一天的秒数
            $endTime = date('Y-m-d H:i:s', $endTime);
            $sendgoodsls = json_decode($gameconfig["allsendgoodsls"], true); //获取总榜单奖励物品
            $sql = "SELECT 
                        actorid, 
                        account, 
                        qufu, 
                        total_num,
                        (@rank := @rank + 1) AS rank_position
                    FROM (
                        SELECT 
                            actorid, 
                            account, 
                            qufu, 
                            SUM(num) AS total_num
                        FROM blackbox_boardlist
                        WHERE qufu >= $startId AND qufu <= $endId AND create_time BETWEEN '$startTime' AND '$endTime'
                        GROUP BY actorid, account, qufu
                ) AS grouped_data 
                CROSS JOIN (SELECT @rank := 0) AS r
                ORDER BY total_num DESC";
            $debugInfo = self::sendMailAndReturn($sql, $sendgoodsls, $type, $kfGroupName, $debugInfo);
        }
        else if ($type == 'day'){
            // 遍历每个跨服组
            foreach ($kfGroups as $kfGroup) {
                $kfGroupName = $kfGroup['kfgroupname'];
                $startRid = $kfGroup['statrid'];
                $endRid = $kfGroup['endid'];
                // 获取奖励物品列表
                $sendgoodsls = json_decode($gameconfig["daysendgoodsls"], true);
                // 构建SQL查询语句
                if ($type == 'day') {
                    $yesterday = date('Y-m-d', strtotime('-1 day'));
                    $sql = "SELECT 
                        actorid, 
                        account, 
                        qufu, 
                        total_num,
                        (@rank := @rank + 1) AS rank_position
                    FROM (
                        SELECT 
                            actorid, 
                            account, 
                            qufu, 
                            SUM(num) AS total_num
                        FROM blackbox_boardlist
                        WHERE qufu >= $startRid AND qufu <= $endRid AND DATE(create_time) = '$yesterday'
                        GROUP BY actorid, account, qufu
                ) AS grouped_data 
                CROSS JOIN (SELECT @rank := 0) AS r
                ORDER BY total_num DESC";
                }
                // 获取用户
                $debugInfo = self::sendMailAndReturn($sql, $sendgoodsls, $type, $kfGroupName, $debugInfo);  //发送并返回展示信息
            }
        }

        // 返回调试信息数组
        return $debugInfo;
    }

    /**
     * 辅助方法------------------发送并返回
     * @param string $sql
     * @param $sendgoodsls
     * @param $type
     * @param string $kfGroupName
     * @param array $debugInfo
     * @return array
     */
    public static function sendMailAndReturn(string $sql, $sendgoodsls, $type, string $kfGroupName, array $debugInfo): array
    {
        // 获取用户
        $players = Db::query($sql);
        // 遍历用户
        foreach ($players as $player) {
            // 获取玩家账号和区服
            $actorid = $player['actorid'];
            $qufu = $player['qufu'];
            // 获取排名
            $rank = $player['rank_position'];

            // 根据排名范围获取对应奖励物品
            $itemsInfo = [];  // 初始化奖励物品信息
            foreach ($sendgoodsls as $range => $data) {
                if (is_string($range) && strpos($range, '-') !== false) {
                    $rangeArray = explode('-', $range);
                    $start = intval($rangeArray[0]);
                    $end = intval($rangeArray[1]);
                } else {
                    $start = $end = intval($range);
                }

                if ($rank >= $start && $rank <= $end) {
                    foreach ($data['items'] as $itemId => $item) {
                        $itemName = $item['name'];
                        $itemNum = $item['num'];

                        // 构造物品信息
                        $itemsInfo[] = [
                            'goodsid' => $itemId,
                            'name' => $itemName,
                            'cjnum' => $itemNum
                        ];
                    }
                    break;  // 找到匹配的排名范围后，跳出循环
                }
            }

            // 发送邮件
            if ($type == 'day') self::sendEmail($qufu, $actorid, $itemsInfo, "盲盒日榜单发放(第" . $rank . "名)");
            if ($type == 'all') self::sendEmail($qufu, $actorid, $itemsInfo, "盲盒总榜单发放(第" . $rank . "名)");

            // 将调试信息添加到数组中
            $debugInfo[] = [
                'actorid' => $actorid,
                'qufu' => $qufu,
                'kfGroupName' => $kfGroupName,
                'rank' => $rank,
                'itemsInfo' => $itemsInfo,
                'sendStatus' => "邮件发送成功"
            ];
        }
        return $debugInfo;
    }


    // 发送指定特权卡物品邮件
    public static function sendEmail($serverid, $actorid, $itemsinfo, $title)
    {
        $chunks = array_chunk($itemsinfo, 10); // 将数组拆分成每个子数组最多包含10个元素
        $sqls = []; // 存储生成的 SQL 语句

        foreach ($chunks as $index => $chunk) {
            $ids = "";
            $names = "";
            foreach ($chunk as $item) {
                $id = $item['goodsid']; // 物品名称
                $name = $item['name']; // 物品名称
                $num = $item['cjnum']; // 物品数量

                if (!empty($ids)) {
                    $ids .= ";";
                }
                $ids .= "1,$id,$num";

                if (!empty($names)) {
                    $names .= ";";
                }
                $names .= "$name*$num";
            }

            $newserverid = self::getDBName($serverid);
            $sql = "INSERT INTO $newserverid.gmcmd (serverid,cmdid,cmd,param1,param2,param3,param4,param5) 
                 VALUES ('$serverid','1','sendMail','$title $index', '$names','$actorid','$ids','');";
            $sqls[] = $sql;
        }

        // 执行生成的 SQL 语句
        foreach ($sqls as $sql) {
            try {
                Db::execute($sql);
                Logg::info("SQL executed successfully: $sql");
            } catch (\Exception $e) {
                Logg::error("SQL execution failed: $sql | Error: " . $e->getMessage());
            }
        }

        // 记录操作日志
        Logg::info("Send email operation completed", [
            'serverid' => $serverid,
            'actorid' => $actorid,
            'itemsinfo' => $itemsinfo,
            'title' => $title,
            'sqls' => $sqls
        ]);

//        // 执行生成的 SQL 语句
//        foreach ($sqls as $sql) {
//            Db::execute($sql);
//        }

        return $sqls; // 返回生成的 SQL 语句数组
    }


    // 获取数据库名称 传入$serverid
    public static function getDBName($serverid)
    {
        $newserverid = 'h5_cq_';
        // 使用正则表达式匹配并获取有效的字符
        $integerValue = intval($serverid) - 1000;
        // 如果有匹配到有效字符，则返回匹配结果，否则返回 "0"
        $trimmedString = $integerValue < 0 ? "0" : strval($integerValue);
        if (env('APP_DEBUG')) {
            $newserverid .= '00' . $trimmedString;
        } else {
            $newserverid .= '' . $trimmedString;
        }

        return $newserverid;
    }

    //获取跨服组配置信息
    public static function getKFServerList()
    {
        $sql = 'SELECT * FROM `blackbox_cfconfig`;';
        $KFlist = Db::query($sql);
        return $KFlist;
    }



}
